﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Adams.Admin.DataAccess.Businesses
{
    public static class Categories
    {
        public static Adams.Admin.DataTransfer.Businesses.Categories.PageLoad PageLoad()
        {
            var data = new Adams.Admin.DataTransfer.Businesses.Categories.PageLoad();
            data.Types = new Shared.Entities.Businesses.TypeCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * FROM Businesses.Types ORDER BY Name
SELECT * FROM Businesses.Categories ORDER BY Name
", CommandType.Text))
            {
                while (dr.Read())
                {
                    var type = new Shared.Entities.Businesses.Type();
                    type.Categories = new Shared.Entities.Businesses.CategoryCollection();
                    type.ID = dr["TypeID"].DbInt();
                    type.Name = dr["Name"].DbString();
                    data.Types.Add(type);
                }
                dr.NextResult();
                while (dr.Read())
                {
                    var cat = new Shared.Entities.Businesses.Category();
                    cat.ID = dr["CategoryID"].DbInt();
                    cat.Name = dr["Name"].DbString();
                    data.Types.Where(t => t.ID == dr["TypeID"].DbInt()).FirstOrDefault().Categories.Add(cat);
                }
            }

            return data;
        }

        public static Adams.Admin.DataTransfer.Businesses.Categories.GetType GetType(Adams.Admin.DataTransfer.Businesses.Categories.GetTypeInput input)
        {
            var data = new Adams.Admin.DataTransfer.Businesses.Categories.GetType();
            data.Type = new Shared.Entities.Businesses.Type();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * FROM Businesses.Types WHERE TypeID = @TypeID
", CommandType.Text,
    DB.CreateParameter("@TypeID", input.TypeID)))
            {
                dr.Read();
                data.Type.ID = dr["TypeID"].DbInt();
                data.Type.Name = dr["Name"].DbString();
            }

            return data;
        }

        public static Adams.Admin.DataTransfer.Businesses.Categories.GetCategory GetCategory(Adams.Admin.DataTransfer.Businesses.Categories.GetCategoryInput input)
        {
            var data = new Adams.Admin.DataTransfer.Businesses.Categories.GetCategory();
            data.Category = new Shared.Entities.Businesses.Category();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * FROM Businesses.Categories WHERE CategoryID = @CategoryID
", CommandType.Text,
    DB.CreateParameter("@CategoryID", input.CategoryID)))
            {
                dr.Read();
                data.Category.ID = dr["CategoryID"].DbInt();
                data.Category.Name = dr["Name"].DbString();
                data.Category.Type = new Shared.Entities.Businesses.Type() {
                    ID = dr["TypeID"].DbInt()
                };
            }

            return data;
        }

        public static int SaveType(DataTransfer.Businesses.Categories.TypeFormInput input)
        {
            return DB.RunScalar(@"
IF(@TypeID = 0)
BEGIN
    INSERT Businesses.Types (Name)
    VALUES (@Name)
    SELECT SCOPE_IDENTITY() AS TypeID
END
ELSE
BEGIN
    UPDATE Businesses.Types
    SET Name = @Name
    WHERE TypeID = @TypeID
    SELECT @TypeID AS TypeID
END
", CommandType.Text,
                DB.CreateParameter("@TypeID", input.ID),
                DB.CreateParameter("@Name", input.Name)).DbInt();
        }

        public static int SaveCategory(DataTransfer.Businesses.Categories.CategoryFormInput input)
        {
            return DB.RunScalar(@"
IF(@CategoryID = 0)
BEGIN
    INSERT Businesses.Categories (TypeID, Name)
    VALUES (@TypeID, @Name)
    SELECT SCOPE_IDENTITY() AS CategoryID
END
ELSE
BEGIN
    UPDATE Businesses.Categories
    SET TypeID = @TypeID, Name = @Name
    WHERE CategoryID = @CategoryID
    SELECT @CategoryID AS CategoryID
END
", CommandType.Text,
                DB.CreateParameter("@CategoryID", input.ID),
                DB.CreateParameter("@TypeID", input.TypeID),
                DB.CreateParameter("@Name", input.Name)).DbInt();
        }

        public static void DeleteType(int typeId)
        {
            DB.Run(@"
DELETE Businesses.Types WHERE TypeID = @TypeID
", CommandType.Text,
    DB.CreateParameter("@TypeID", typeId));
        }

        public static void DeleteCategory(int categoryId)
        {
            DB.Run(@"
DELETE Businesses.Categories WHERE CategoryID = @CategoryID
", CommandType.Text,
    DB.CreateParameter("@CategoryID", categoryId));
        }
    }
}
